package vfs.persist.association.impl;

import java.sql.SQLException;
import vfs.persist.association.VFSAssociationManager;
import vfs.persist.VFSIterator;
import vfs.filter.VFSFilter;
import java.util.Iterator;
import java.sql.ResultSet;
import vfs.persist.entity.build.impl.VFSEntityBuilderImpl;
import vfs.except.VFSException;
import vfs.obj.entity.VFSEntity;
import java.sql.Connection;

import static vfs.util.VFSPersistUtil.*;

/**
 *
 * @author Matthew Eavenson <durandal@uga.edu>
 */
public class VFSAssociationManagerImpl<T extends VFSEntity, U extends VFSEntity> implements VFSAssociationManager<T,U>{

    private final VFSEntityBuilderImpl<T> emT;
    private final VFSEntityBuilderImpl<U> emU;
    public final String TABLE;
    public final String TLINK;
    public final String ULINK;
    private final Connection conn;

    public VFSAssociationManagerImpl(Connection conn, VFSEntityBuilderImpl<T> emT, 
                                                      VFSEntityBuilderImpl<U> emU, 
                                      String table, String tLink, String uLink) {
        this.conn = conn;
        this.emT = emT;
        this.emU = emU;
        TABLE = table;
        TLINK = tLink;
        ULINK = uLink;
    }

    /*****************************************************************************
     * Find the unique class-T object referenced from the class-U object with the
     * given primary key.
     * For 1-to-M associations returning a class-T object on the one-side.
     * Ex: 
     * Query: 
     * @param uObj  the class-U object
     * @return      a referenced class-T object
     */
    @Override
    public T findT1M(U uObj) throws VFSException {
        if(uObj == null) throw new VFSException("FindT 1-to-Many: no object given");
        long uid = uObj.getId();
        String query = "SELECT "+emT.TABLE+"."+emT.KEY+", "+emT.FIELDS+
                      " FROM "+emT.TABLE+" JOIN "+TABLE+
                      " ON " + TLINK + " = "+emT.TABLE+"."+emT.KEY+ 
                      " WHERE "+emU.TABLE+"."+emU.KEY+" = "+uid;
        ResultSet rs = queryDB(conn, query);
        try{
            if(rs.next()) return buildT(rs);
        }catch(SQLException sqe){
            throw new VFSException(sqe.getMessage());
        }
        return null;
    }

    /*****************************************************************************
     * Find the unique class-U object referenced from the class-T object with the
     * given primary key.
     * For M-to-1 associations returning a class-U object on the one-side.
     * Ex: many-to-one ("\User\", "worksfor", "laboratory")
     * Query: for a given user, find the unique laboratory
     * @param tObj  the class-T object
     * @return  a referenced class-U object
     */
    @Override
    public U findUM1(T tObj) throws VFSException {
        if(tObj == null) throw new VFSException("FindU Many-to-1: no object given");
        long tid = tObj.getId();
        String query = "SELECT "+emU.TABLE+"."+emU.KEY+", "+emU.FIELDS+
                      " FROM "+emU.TABLE+" JOIN "+TABLE+
                      " ON " + ULINK + " = "+emU.TABLE+"."+emU.KEY+ 
                      " WHERE "+emT.TABLE+"."+emT.KEY+" = "+tid;
        ResultSet rs = queryDB(conn, query);
        try{
            if(rs.next()) return buildU(rs);
        }catch(SQLException sqe){
            throw new VFSException(sqe.getMessage());
        }
        return null;
    }

    /*****************************************************************************
     * Find the class-T objects referenced from the class-U object with the given
     * primary key (iterator version).
     * For M-to-1 associations returning class-T objects on the many-side.
     * @param uObj    the class-U object
     * @param filterT  the constraint used to filter the results (class-T objects)
     * @return        an iterator to the referenced class-T objects
     */
    @Override
    public Iterator<T> findTM1(U uObj, VFSFilter filter) throws VFSException {
        if(uObj == null) throw new VFSException("FindT Many-to-1: no object given");
        long uid = uObj.getId();
        if(filter != null) filter.setPrefix(" AND ");
        String query = "SELECT "+emT.TABLE+"."+emT.KEY+", "+emT.FIELDS+
                      " FROM " + emT.TABLE+" JOIN "+TABLE+
                      " WHERE " + ULINK + " = " + uid + (filter == null ? "" : filter);
        return getTs(query);
    }

    /*****************************************************************************
     * Find the class-U objects referenced from the class-T object with the given
     * primary key (iterator version).
     * For 1-to-M associations returning class-U objects on the many-side.
     * @param tObj        the class-T object
     * @param filterU      the constraint used to filter the results (class-U objects)
     * @return  an iterator to the referenced class-U objects
     */
    @Override
    public Iterator<U> findU1M(T tObj, VFSFilter filter) throws VFSException {
        if(tObj == null) throw new VFSException("FindU 1-to-Many: no object given");
        long tid = tObj.getId();
        if(filter != null) filter.setPrefix(" AND ");
        String query = "SELECT "+emU.TABLE+"."+emU.KEY+", "+emU.FIELDS+
                      " FROM " + emU.TABLE+" JOIN "+TABLE+
                      " WHERE " + TLINK + " = " + tid + (filter == null ? "" : filter);
        return getUs(query);
    }

    /*****************************************************************************
     * Find the class-T objects from the M-to-M relationship table given the class-U's
     * primary key (iterator version).
     * For M-to-M associations, returning class-T objects.
     * @param uObj        the class-U object
     * @param filterT     the constraint used to filter the results (class-T objects)
     * @return  a list of class-T objects
     */
    @Override
    public Iterator<T> findTMM(U uObj, VFSFilter filter) throws VFSException {
        if(uObj == null) throw new VFSException("FindT Many-to-Many: no object given");
        long uid = uObj.getId();
        if(filter != null) filter.setPrefix(" AND ");
        String query = "SELECT "+emT.TABLE+"."+emT.KEY+", "+emT.FIELDS+
                      " FROM "+emT.TABLE+" JOIN "+TABLE+" ON "+TLINK+" = "+emT.KEY +
                      " WHERE "+ULINK+" = " + uid + (filter == null ? "" : filter);
        return getTs(query);
    }

    /*****************************************************************************
     * Find the class-U objects from the M-to-M relationship table given the class-T's
     * primary key (iterator version).
     * For M-to-M associations, returning class-U objects.
     * @param tObj        the class-T object
     * @param filterU     the constraint used to filter the results (class-U objects)
     * @return  a list of class-U objects
     */
    @Override
    public Iterator<U> findUMM(T tObj, VFSFilter filter) throws VFSException {
        if(tObj == null) throw new VFSException("FindU Many-to-Many: no object given");
        long tid = tObj.getId();
        if(filter != null) filter.setPrefix(" AND ");
        String query = "SELECT "+emU.TABLE+"."+emU.KEY+", "+emU.FIELDS+
                      " FROM "+emU.TABLE+" JOIN "+TABLE+" ON "+ULINK+" = "+emU.KEY +
                      " WHERE "+TLINK+" = " + tid + (filter == null ? "" : filter);
        return getUs(query);
    }

    /*****************************************************************************
     * Make the association between class-T and class-U objects persistent by saving
     * it into the database.
     * For M-to-1 associations.
     * @param entityT  the class-T entity (many side)
     * @param entityU  the class-U entity (one side)
     */
    @Override
    public void persistM1(T entityT, U entityU) throws VFSException {
        long tid = entityT.getId();
        long uid = entityU.getId();
        String update = "UPDATE " + emT.TABLE +
                       " SET " + ULINK + " = " + uid +
                       " WHERE " + emT.KEY + " = " + tid;
        updateDB(conn, update);
    }

    /*****************************************************************************
     * Make the association between class-T and class-U objects persistent by saving
     * it into the database.
     * For 1-to-M associations.
     * @param entityT  the class-T entity (one side)
     * @param entityU  the class-U entity (many side)
     */
    @Override
    public void persist1M(T entityT, U entityU) throws VFSException {
        long tid = entityT.getId();
        long uid = entityU.getId();
        String update = "UPDATE " + emU.TABLE +
                       " SET " + TLINK + " = " + tid +
                       " WHERE " + emU.KEY + " = " + uid;
        updateDB(conn, update);
    }

    /*****************************************************************************
     * Make the association between class-T and class-U objects persistent by saving
     * it into the database.
     * For M-to-M associations.
     * @param entityT  the class-T entity (many side)
     * @param entityU  the class-U entity (many side)
     */
    @Override
    public void persistMM(T entityT, U entityU) throws VFSException {
        long tid = entityT.getId();
        long uid = entityU.getId();
        String update = "INSERT INTO " + TABLE + " (" + TLINK + "," + ULINK + ")" +
                       " VALUES ( " + tid + ", " + uid + " )";
        updateDB(conn, update);
    }

    /*****************************************************************************
     * Remove the class-T x class-U association from the database, by setting the
     * class-T object's foreign key to null.
     * For M-to-0 associations (i.e., one-side is optional).
     * @param entity  the class-T object
     */
    @Override
    public void removeM0(T entity) throws VFSException {
        long tid = entity.getId();
        String update = " UPDATE " + emU.TABLE +
                        " SET " + TLINK + " = null" +
                        " WHERE " + emU.KEY + " = " + tid;
        updateDB(conn, update);
    }

    /*****************************************************************************
     * Remove the class-T x class-U association from the database.  This is not
     * supported since it would violate a NOT NULL constraint in the database.
     * For M-to-1 associations (i.e., one-side is mandatory).
     * @param entityT  the entity T
     * @param entityU  the entity U
     */
    @Override
    public void removeM1(T entityT, U entityU) throws VFSException {
        throw new VFSException("Remove: not allowed for many-to-one associations");
    }

    /*****************************************************************************
     * Remove the class-T x class-U association from the database, by setting the
     * class-U object's foreign key to null.
     * For 0-to-M associations (i.e., one-side is optional).
     * @param entity  the class-U object
     */
    @Override
    public void remove0M(U entity) throws VFSException {
        long uid = entity.getId();
        String update = " UPDATE " + emT.TABLE +
                        " SET " + ULINK + " = null" +
                        " WHERE " + emT.KEY + " = " + uid;
        updateDB(conn, update);
    }

    /*****************************************************************************
     * Remove the class-T x class-U association from the database.  This is not
     * supported since it would violate a NOT NULL constraint in the database.
     * For M-to-1 associations (i.e., one-side is mandatory).
     * @param entityT  the entity T
     * @param entityU  the entity U
     */
    @Override
    public void remove1M(T entityT, U entityU) throws VFSException {
        throw new VFSException("Remove: not allowed for many-to-one associations");
    }

    /*****************************************************************************
     * Remove the class-T x class-U association from the database, by deleting
     * their entry in the relationship table.
     * For many-to-many associations.
     * @param entityT  the class-T object
     * @param entityU  the class-U object
     */
    @Override
    public void removeMM(T entityT, U entityU) throws VFSException {
        long tid = entityT.getId();
        long uid = entityU.getId();
        String update = "DELETE FROM " + TABLE +
                       " WHERE " + TLINK + " = " + tid +
                       " AND " + ULINK + " = " + uid;
        updateDB(conn, update);
    }
    
    protected U buildU(ResultSet rs) throws VFSException{
        return emU.build(rs);
    }
    
    protected T buildT(ResultSet rs) throws VFSException{
        return emT.build(rs);
    }

    /*****************************************************************************
     * Load the class-T objects that satisfy the SQL query (iterator version).
     * @param query  the SQL query used to retrieve results
     * @return  an iterator to the class-T objects in the query result
     */
    protected Iterator<U> getUs(String query) throws VFSException {
        ResultSet rs = queryDB(conn, query);
        return new VFSIterator<U>(rs, emU);
    }

    protected Iterator<T> getTs(String query) throws VFSException {
        ResultSet rs = queryDB(conn, query);
        return new VFSIterator<T>(rs, emT);
    }
}
